package com.seeapp.backend.file;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.reflect.FieldUtils;
import org.apache.http.client.utils.DateUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.fasterxml.jackson.annotation.JsonFormat;

import io.swagger.annotations.ApiModelProperty;

/**
 * Excel操作工具 <br>
 * 该工具类不会主动关闭流<br>
 * 注意：采用的反射机制，获取字段只能获取当前类声明或者继承的字段<br>
 * &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;如果类嵌套类则无法处理
 * 
 * @author xinlin.wang
 *
 */
public class ExcelProcessor {
	/**
	 * 加速头部bean反射
	 */
	private static Map<Class<?>, Map<String, String>> voInfoCache = new ConcurrentHashMap<Class<?>, Map<String, String>>();
	private List<String> beanFieldName;
	private int currentRowIndex = 0;
	private int columnCount = 0;
	private String[] headArray;
	private Workbook workbook;
	private OutputStream out;
	private InputStream in;
	private String title;
	private Sheet sheet;
	private Cell cell;
	private Row row;

	public ExcelProcessor(final String excelTemplateName, OutputStream out) throws Exception {
		this.in = FileUtil.getExcelTemplateStream(excelTemplateName);
		if (excelTemplateName.endsWith("xls")) {
			workbook = new HSSFWorkbook(in);
		} else if (excelTemplateName.endsWith("xlsx")) {
			workbook = new XSSFWorkbook(in);
		} else {
			throw new IllegalArgumentException("不支持的文件格式");
		}
		this.out = out;
		sheet = workbook.getSheetAt(0);
	}

	/**
	 * 创建新的sheet
	 * 
	 * @param sheetName
	 */
	public ExcelProcessor creatNewSheet(String sheetName) {
		if (StringUtils.isEmpty(sheetName)) {
			sheet = workbook.createSheet();
		} else {
			sheet = workbook.createSheet(sheetName);
		}
		// 复制title和head
		CellRangeAddress cra = new CellRangeAddress(0, 0, 0, columnCount);
		sheet.addMergedRegion(cra);
		sheet.getRow(0).getCell(0).setCellValue(this.title);
		row = sheet.createRow(1);
		for (int index = 0; index < columnCount; index++) {
			cell = row.createCell(index);
			setCellValue(headArray[index]);
		}
		currentRowIndex = 2;
		row = sheet.createRow(currentRowIndex);
		cell = row.createCell(0);
		return this;
	}

	/**
	 * 设置excel头部信息
	 * 
	 * @param title
	 */
	public ExcelProcessor setTitle(String title) {
		row = sheet.getRow(0);
		cell = row.getCell(0);
		String titleFlag = cell.getStringCellValue();
		if ("#title#".equals(titleFlag)) {// 代表模板需要设置标题
			this.title = title;
			setCellValue(title);
			currentRowIndex++;
		}
		columnCount = sheet.getRow(currentRowIndex).getLastCellNum();// 设置两次，因为有的模板没有标题
		return this;
	}

	/**
	 * 利用swagger中@ApiModelPropert的中文注解获取需要设置的excel头部信息
	 * 
	 * @param bean
	 */
	public ExcelProcessor setHead(Class<?> bean) {
		row = sheet.getRow(currentRowIndex);
		columnCount = sheet.getRow(currentRowIndex).getLastCellNum();// 设置两次，因为有的模板没有标题
		String[] head = setRowFieldInfo(bean);
		setHead(head);
		return this;
	}

	/**
	 * 设置excel头部信息
	 * 
	 * @param headArray
	 */
	public ExcelProcessor setHead(String... headArray) {
		this.headArray = headArray;
		row = sheet.getRow(currentRowIndex);
		for (int index = 0; index < columnCount; index++) {
			cell = row.getCell(index);
			setCellValue(headArray[index]);
		}
		currentRowIndex++;
		return this;
	}

	/**
	 * 获取需要设置的字段
	 */
	private String[] setRowFieldInfo(Class<? extends Object> beanClass) {
		String[] result = new String[columnCount];
		Map<String, String> allFieldDesc = getAllFieldAnnotationValue(beanClass);
		row = sheet.getRow(currentRowIndex);
		beanFieldName = new ArrayList<String>(columnCount);
		String cellField = null;
		for (int index = 0; index < columnCount; index++) {
			cellField = row.getCell(index).getStringCellValue();
			beanFieldName.add(cellField);// 保存bean字段，在外部存储因为使用多次
			result[index] = allFieldDesc.get(cellField);// 返回head，只用一次
		}
		return result;
	}

	/**
	 * 获取该bean上所有ApiModelProperty注解的中文描述
	 * 
	 * @param beanType
	 * @return
	 */
	private static Map<String, String> getAllFieldAnnotationValue(Class<? extends Object> beanClass) {
		if (voInfoCache.containsKey(beanClass)) {
			return voInfoCache.get(beanClass);
		} else {
			Map<String, String> fieldDesc = new HashMap<String, String>();
			for (Field field : FieldUtils.getAllFields(beanClass)) {
				fieldDesc.put(field.getName(), field.getAnnotation(ApiModelProperty.class).value());
			}
			voInfoCache.put(beanClass, fieldDesc);
			return fieldDesc;
		}
	}

	/**
	 * 追加数据
	 * 
	 * @param list
	 * @throws IOException
	 * @throws @throws
	 *             InvocationTargetException
	 * @throws IllegalAccessException
	 */
	public ExcelProcessor append(List<?> list) throws Exception {
		if (CollectionUtils.isEmpty(list)) {
			return this;
		}
		String value = null;
		Field field = null;
		Object fieldData = null;
		JsonFormat jsonFormat = null;
		for (Object item : list) {
			row = sheet.createRow(currentRowIndex++);
			for (int index = 0; index < columnCount; index++) {
				cell = row.createCell(index, Cell.CELL_TYPE_STRING);
				field = FieldUtils.getField(item.getClass(), beanFieldName.get(index), true);// 继承的字段也可以获取到
				if (field == null) {
					setCellValue("");
					continue;
				}

				fieldData = field.get(item);
				if (field.getType() == Date.class) {
					jsonFormat = field.getAnnotation(JsonFormat.class);
					if (fieldData != null) {
						value = DateUtils.formatDate((Date) fieldData,jsonFormat == null ? "yyyy-MM-dd" : jsonFormat.pattern());
					}
				} else {
					value = fieldData == null ? "" : fieldData.toString();
				}
				setCellValue(value);
			}
		}
		out.flush();
		return this;
	}

	/**
	 * 设置单元格格式-字符编码等
	 * 
	 * @param value
	 */
	private void setCellValue(String value) {
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setCellValue(value);
	}

	/**
	 * 关闭资源
	 * 
	 * @throws IOException
	 */
	public void flush() throws IOException {
		try {
			workbook.write(out);
		} catch (IOException e) {
			throw e;
		} finally {
			try {
				out.flush();
			} finally {
				if (in != null) {
					in.close();
				}
			}
		}
	}

}
